package com.red.wood.module.task;

import java.io.File;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.red.wood.common.ip.ipipnet.Reader;
import com.red.wood.common.springjdbc.CommonDao;
import com.red.wood.common.util.CommonUtils;
import com.red.wood.common.util.JarToolUtil;
import com.red.wood.common.util.ProvinceParse;

@Service
public class IrcsProvinceParseService {

	private static final Log log = LogFactory.getLog(IrcsProvinceParseService.class);
	
	@Autowired
	private CommonDao dao;
	
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * IRCS企业根据地址找到所属省份
	 */
	public void ircsDataParse() {
		try {
			log.info("=============IRCS企业信息插入开始==============");
			StringBuilder sqlSb = new StringBuilder("select uuid,ircsid,ircsname,ircsadd,ircszip,corp,housecount,usercount,commandid,provinceid,timstamp,filecreatetime from t_dwd_ircs_ircsdata where provinceid is null");
			List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
			
			for(Map<String,Object> map : list) {
				String ircsAdd = CommonUtils.valueOf(map.get("IRCSADD"));
			
				String province = ProvinceParse.addressAdscription(ircsAdd);
				map.put("PROVINCEID", CommonUtils.getProvinceId(province));
			}
			dao.deleteTableData("delete from t_dwd_ircs_ircsdata where provinceid is null");
			String sql = "insert into t_dwd_ircs_ircsdata(uuid,ircsid,ircsname,ircsadd,ircszip,corp,housecount,usercount,commandid,provinceid,timstamp,filecreatetime) values (?,?,?,?,?,?,?,?,?,?,?,?)";
			String columns = "uuid,ircsid,ircsname,ircsadd,ircszip,corp,housecount,usercount,commandid,provinceid,timstamp,filecreatetime";
			dao.insertBatchTableData(list, sql, columns,10000);
			log.info("=============IRCS企业信息插入结束==============");
		}catch(Exception e) {
			log.error("=============IRCS企业信息插入异常==============");
			if(log.isErrorEnabled()) {
				log.error(e.getMessage(),e);
			}
		}
	}
	
	/**
	 * IRCS用户根据地址找到所属省份
	 */
	public void ircsUserParse() {
		try {
			log.info("=============IRCS用户信息插入开始==============");
			int min = 0;
			int max = 10000;
			int pageSize = 10000;
			int totalCount = 0;
			dao.deleteTableData("delete from t_dwd_ircs_userinfo_temp");
			do{
				//由于数据量比较大，故做分页处理,先将符合的数据插入临时表，然后在从临时表中插入用户表，每次处理10000条数据
				List<Map<String,Object>> list = getUserInfo(min, max);
				for(Map<String,Object> map : list) {
					String address = CommonUtils.valueOf(map.get("ADDRESS"));
				
					String province = ProvinceParse.addressAdscription(address);
					map.put("PROVINCEID", CommonUtils.getProvinceId(province));
				}
				String sql = "insert into t_dwd_ircs_userinfo_temp(uuid,ircsid,useruuid,userid,nature,unitname,idtype,idno,unitnature,officermobile,officertel,officeremail,address,zipcode,registertime,provinceid,timstamp,filecreatetime) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				String columns = "uuid,ircsid,useruuid,userid,nature,unitname,idtype,idno,unitnature,officermobile,officertel,officeremail,address,zipcode,registertime,provinceid,timstamp,filecreatetime";
				
				dao.insertBatchTableData(list, sql, columns,10000);
				totalCount = list.size();
				min = max;
				max = max + pageSize;
			}while(pageSize == totalCount);
			
			dao.deleteTableData("delete from t_dwd_ircs_userinfo_result where provinceid is null and address is not null");
			
			String sql ="insert into t_dwd_ircs_userinfo_result select * from t_dwd_ircs_userinfo_temp";
			jdbcTemplate.execute(sql);
			
			log.info("============IRCS用户信息插入完成==============");
		}catch(Exception e) {
			log.error("=============IRCS用户信息插入异常==============");
			if(log.isErrorEnabled()) {
				log.error(e.getMessage(),e);
			}
		}
	}
	
	/**
	 * IP信息根据起始IP找到对应所属省份
	 */
	public void ircsIpParse() {
		try {
			log.info("=============IRCS IP信息插入开始==============");
			StringBuilder sqlSb = new StringBuilder("select uuid,ircsid,ipinfouuid,ipinfoid,startip,endip,type,sourceunit,allocationunit,allocationtime,provinceid,timstamp,filecreatetime from t_dwd_ircs_ipinfo where provinceid is null"); 
			List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
			String path = File.separator + "ipiptest.ipdb";
			Reader db = null;
			if(JarToolUtil.isWindows()) {
				db = new Reader(path);
			}else {
				db = new Reader(JarToolUtil.getJarDir() + path);
			}
			for(Map<String,Object> map : list) {
				String startIp = CommonUtils.valueOf(map.get("STARTIP"));
				map.put("PROVINCEID", CommonUtils.getProvinceId(db.find(startIp, "CN")[1]));
			}
			dao.deleteTableData("delete from t_dwd_ircs_ipinfo where provinceid is null");
			String sql = "insert into t_dwd_ircs_ipinfo(uuid,ircsid,ipinfouuid,ipinfoid,startip,endip,type,sourceunit,allocationunit,allocationtime,provinceid,timstamp,filecreatetime) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
			String columns = "uuid,ircsid,ipinfouuid,ipinfoid,startip,endip,type,sourceunit,allocationunit,allocationtime,provinceid,timstamp,filecreatetime";
			dao.insertBatchTableData(list, sql, columns,5000);
			log.info("=============IRCS IP信息插入结束==============");
		}catch(Exception e) {
			log.error("=============IRCS IP信息插入异常==============");
			if(log.isErrorEnabled()) {
				log.error(e.getMessage(),e);
			}
		}
	}
	
	/**
	 * 链路信息根据链路IP找到所属省份
	 */
	public void ircsIpGetWayParse(){
		try {
			log.info("=============IRCS链路信息插入开始==============");
			StringBuilder sqlSb = new StringBuilder("select ircsid,gatewayuuid,gatewayid,bandwidth,linktype,accessunit,gatewayip,internetaccess,provinceid,timstamp,filecreatetime from t_dwd_ircs_gatewayinfo where provinceid is null");
			List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
			String path = File.separator + "ipiptest.ipdb";
			Reader db = null;
			if(JarToolUtil.isWindows()) {
				db = new Reader(path);
			}else {
				db = new Reader(JarToolUtil.getJarDir() + path);
			}
			for(Map<String,Object> map : list) {
				String gatewayIp = CommonUtils.valueOf(map.get("GATEWAYIP"));
				map.put("PROVINCEID", CommonUtils.getProvinceId(db.find(gatewayIp, "CN")[1]));
			}
			dao.deleteTableData("delete from t_dwd_ircs_gatewayinfo where provinceid is null");
			String sql = "insert into t_dwd_ircs_gatewayinfo(ircsid,gatewayuuid,gatewayid,bandwidth,linktype,accessunit,gatewayip,internetaccess,provinceid,timstamp,filecreatetime) values (?,?,?,?,?,?,?,?,?,?,?)";
			String columns = "ircsid,gatewayuuid,gatewayid,bandwidth,linktype,accessunit,gatewayip,internetaccess,provinceid,timstamp,filecreatetime";
			dao.insertBatchTableData(list, sql, columns,10000);
			log.info("=============IRCS链路信息插入结束==============");
		}catch(Exception e) {
			log.error("=============IRCS链路信息插入异常==============");
			if(log.isErrorEnabled()) {
				log.error(e.getMessage(),e);
			}
		}
	}
	
	public List<Map<String,Object>> getUserInfo(int min,int max) {
		StringBuilder sql = new StringBuilder();
		sql.append("select *");
		sql.append("  from (select a.*, rownum rn");
		sql.append("          from (select uuid,ircsid,useruuid,userid,nature,unitname,idtype,idno,unitnature,officermobile,officertel,officeremail,address,zipcode,registertime,provinceid,timstamp,filecreatetime from t_dwd_ircs_userinfo_result where provinceid is null and address is not null order by uuid) a");
		sql.append("         where rownum <= "+max+") aa");
		sql.append("  where rn > "+min);
		List<Map<String, Object>> list = dao.queryTableData(null, sql);
		return list;
	}
}